"""
Case Type   : index blurred query
Case Name   : 设置schema不指定字符集,table指定支持字符集,字段指定支持字符集,验证字段模糊查询走索引
Create At   : 2024/10/15
Owner       : lvlintao666
Description :
    1、创建表及索引
    2、查看执行计划及查询结果
    3、清理环境
Expect      :
    1、成功
    2、成功
    3、清理成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class IndeBlurredTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbBmode')
        self.create_tb = f"set b_format_behavior_compat_options = enable_multi_charset;" \
                         f"drop table if exists t_table_name_1;" \
                         f"drop table if exists t_table_name_2;" \
                         f"create table t_table_name_1(c1 text charset GB18030 collate gb18030_chinese_ci);" \
                         f"create table t_table_name_2(c1 char(50) charset GB18030 collate gb18030_chinese_ci);" \
                         f"insert into t_table_name_1 values('sdeWEFWE34523421')," \
                         f"('werWEFWE541512412');" \
                         f"insert into t_table_name_2 values('sdeWEFWE34523421')," \
                         f"('werWEFWE541512412');" \
                         f"create index in_index_name_1 on t_table_name_1(c1(20));" \
                         f"create index in_index_name_2 on t_table_name_2(c1);"
        self.query_sql1 = f"set enable_seqscan=off;" \
                          f"set b_format_behavior_compat_options = enable_multi_charset;" \
                          f"select * from t_table_name_1 where c1 like 'sde%';" \
                          f"explain select * from t_table_name_1 where c1 like 'sde%';"
        self.query_sql2 = f"set enable_seqscan=off;" \
                          f"set b_format_behavior_compat_options = enable_multi_charset;" \
                          f"select * from t_table_name_2 where c1 like 'sde%';" \
                          f"explain select * from t_table_name_2 where c1 like 'sde%';"
        self.clean_tb = f"drop index in_index_name_1;" \
                        f"drop index in_index_name_2;" \
                        f"drop table if exists t_table_name_1;" \
                        f"drop table if exists t_table_name_2;"
        self.flag1 = f"sdeWEFWE34523421"
        self.flag2 = f"Index Scan using in_index_name_1 on t_table_name_1"
        self.flag3 = f"Index Only Scan using in_index_name_2 on t_table_name_2"

    def test_index(self):
        text = '--step1:创建表及索引;expect: 成功--'
        logger.info(text)
        tb_res = self.comsh.execut_db_sql(self.create_tb)
        logger.info(tb_res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, tb_res,
                      '执行失败' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, tb_res,
                      '执行失败' + text)

        text = '--step2:查看执行计划及查询结果;expect: 走索引--'
        logger.info(text)
        query_res = self.comsh.execut_db_sql(self.query_sql1)
        logger.info(query_res)
        self.assertIn(self.flag1, query_res, '执行失败' + text)
        self.assertIn(self.flag2, query_res, '执行失败' + text)
        query_res = self.comsh.execut_db_sql(self.query_sql2)
        logger.info(query_res)
        self.assertIn(self.flag1, query_res, '执行失败' + text)
        self.assertIn(self.flag3, query_res, '执行失败' + text)

    def tearDown(self):
        text = '--step3:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
